package com.yls.except.dao;

import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 商家商品dao
 * Created by Administrator on 2016/8/4.
 */
@Repository(value = "shopDao")
public interface ShopDao {

    /**
     * 获取首页故事列表信息
     *
     * @param storymap 查询条件及分页条件
     * @return 首页故事列表信息
     */
    @Select("select story_id,story_name,brief,story_logo,type,shopid from yls_story where status=1 and isdisabled=1 ${storytype} order by sticktime desc limit #{start},#{count}")
    List<Map<String, Object>> selectStoryListInfo(Map<String, Object> storymap);

    /**
     * 根据商品id查询商品信息
     *
     * @param shopid 商品id
     * @return 商品信息
     */
    @Select("select shop_id,shop_name,shop_desc,shop_weight,sold,expired,addaid,addtime,addip,status,remark,isdisabled,freight_id from yls_shopping where shop_id=#{shopid} and isdisabled=1 and status=1")
    Map<String, Object> selectShopInfoById(String shopid);

    /**
     * 根据故事id查询故事详情
     *
     * @param storyid 故事id
     * @return 故事详情
     */
    @Select("select story_id,username,story_name,brief,story_logo,`desc`,type,shopid from yls_story where story_id=#{storyid} and status=1 and isdisabled=1")
    Map<String, Object> selectStoryInfoById(String storyid);

    /**
     * 根据故事id查询故事评价信息
     *
     * @param map
     * @return
     */
    @Select("select e.evaluate_id,e.story_id,e.uid,e.evaluate_desc,e.addtime,u.headimg,u.nickname " +
            "from yls_evaluate e, yls_user u " +
            "where e.story_id=#{storyid} and e.status=1 and e.isdisabled=1 and u.uid=e.uid " +
            "order by addtime desc limit #{start},#{count}")
    List<Map<String, Object>> selectEvaluateByStoryId(Map<String, Object> map);

    /**
     * 根据用户id和评价id查询点赞总条数
     *
     * @param praise 评价id和用户id
     * @return 点赞总条数
     */
    @Select("select ifnull(count(*), 0) from yls_praise where evaluate_id=#{evaluate_id} and uid=#{uid} and status=1 and isdisabled=1")
    int selectEvaluatePraiseByEvaluateAndUid(Map<String, Object> praise);

    /**
     * 新增故事评价信息
     *
     * @param evaluate 评价信息
     * @return 新增状态（大于0成功，否则失败）
     */
    @Insert("insert into yls_evaluate(evaluate_id,story_id,uid,evaluate_desc,addtime,addip,status,isdisabled) " +
            "values(#{evaluate_id},#{story_id},#{uid},#{evaluate_desc},#{addtime},#{addip},#{status},#{isdisabled})")
    int insertStoryEvaluate(Map<String, Object> evaluate);

    /**
     * 根据评价id查询回复信息
     *
     * @param evaluate_id 评价id
     * @return 回复信息
     */
    @Select("select reply_id,evaluate_id,id,type,reply_text,reply_time from yls_evaluate_reply where evaluate_id=#{evaluate_id} and isdisabled=1")
    List<Map<String, Object>> selectEvaluateReplyByEvaluateId(String evaluate_id);

    /**
     * 根据评价id查询评价信息
     *
     * @param evaluateid 评价id
     * @return 评价信息
     */
    @Select("select evaluate_id,story_id,uid,evaluate_desc,addtime,addip,status,isdisabled from yls_evaluate where evaluate_id=#{evaluateid} and isdisabled=1")
    Map<String, Object> selectEvaluateInfoById(String evaluateid);

    /**
     * 根据评价id和用户id查询用户点赞信息
     *
     * @param praiseMap 评价id和用户id
     * @return 用户点赞信息
     */
    @Select("select evaluate_id,uid,status,addtime,addip,isdisabled from yls_praise where evaluate_id=#{evaluateid} and uid=#{uid} and isdisabled=1")
    Map<String, Object> selectEvaluatePraiseInfoByUidAndEvaluateId(Map<String, Object> praiseMap);

    /**
     * 新增
     *
     * @param praiseinfo 点赞信息
     * @return 新增状态（大于0成功，否则失败）
     */
    @Insert("insert into yls_praise" +
            "(evaluate_id,uid,status,addtime,addip,isdisabled) " +
            "values " +
            "(#{evaluate_id},#{uid},#{status},#{addtime},#{addip},#{isdisabled})")
    int insertEvaluatePraise(Map<String, Object> praiseinfo);

    /**
     * 修改点赞信息
     *
     * @param praiseinfo 点赞信息
     * @return 修改状态（大于0成功，否则失败）
     */
    @Update("update yls_praise set uid=#{uid},status=#{status},addtime=#{addtime},addip=#{addip},isdisabled=#{isdisabled} where evaluate_id=#{evaluate_id}")
    int updateEvaluatePraise(Map<String, Object> praiseinfo);

    /**
     * 根据故事id和用户id查询收藏信息
     *
     * @param collect 故事id和用户id
     * @return 收藏信息
     */
    @Select("select story_id,uid,status,addtime,addip,isdisabled from yls_collect where story_id=#{storyid} and uid=#{uid}")
    Map<String, Object> selectStoryCollectByStoryIdAndUid(Map<String, Object> collect);

    /**
     * 新增故事收藏信息
     *
     * @param collectInfo 收藏信息
     * @return 新增状态（大于0成功，否则失败）
     */
    @Insert("insert into yls_collect " +
            "(story_id,uid,status,addtime,addip,isdisabled) " +
            "values " +
            "(#{story_id},#{uid},#{status},#{addtime},#{addip},#{isdisabled})")
    int insertStoryCollect(Map<String, Object> collectInfo);

    /**
     * 修改故事收藏信息
     *
     * @param collectInfo 收藏信息
     * @return 修改状态（大于0成功，否则失败）
     */
    @Update("update yls_collect set uid=#{uid},status=#{status},addtime=#{addtime},addip=#{addip},isdisabled=#{isdisabled} where story_id=#{story_id}")
    int updateStoryCollect(Map<String, Object> collectInfo);

    /**
     * 根据商品id查询商品规格价格信息
     *
     * @param shopid 商品id
     * @return 商品规格价格信息
     */
    @Select("select p.shop_spec_price_id,p.shop_id,p.spec_id,p.price,p.first,p.second,p.third,p.forth,p.fifth,p.img,s.name from yls_shop_spec s,yls_shop_spec_price p where p.shop_id=#{shopid} and p.isdisabled=1 and p.spec_id=s.spec_id ")
    List<Map<String, Object>> selectShopSpecPriceByShopId(String shopid);

    /**
     * 根据商品id查询商品优惠价格信息
     *
     * @param shopid 商品id
     * @return 商品优惠价格信息
     */
    @Select("select favorabled_id,shop_id,number,lose from yls_shop_favorable where shop_id=#{shopid} and isdisabled=1 order by addtime")
    List<Map<String, Object>> selectShopFavorabledByShopId(String shopid);

    /**
     * 根据评价id查询点赞总条数
     *
     * @param evaluate_id 评价id
     * @return 点赞总条数
     */
    @Select("select ifnull(count(*), 0) from yls_praise where evaluate_id=#{evaluateid} and isdisabled=1")
    int selectEvaluatePraiseCountByEvaluateId(String evaluate_id);

    /**
     * 新增购物车
     *
     * @param shopcart 购物车
     * @return 购物车信息
     */
    @Insert("insert into yls_shopcart (shopcart_id,uid,shop_id,num,bady_sex,bady_age_id,shop_spec_price,status,addtime,addip,isdisabled) " +
            "values (#{shopcart_id},#{uid},#{shop_id},#{num},#{bady_sex},#{bady_age_id},#{shop_spec_price},#{status},#{addtime},#{addip},#{isdisabled})")
    int insertShopCart(Map<String, Object> shopcart);

    /**
     * 根据用户id、商品id、宝宝性别、宝宝年龄id、商品规格价格id查询购物车信息
     *
     * @param shopcart 用户id、商品id、宝宝性别、宝宝年龄id、商品规格价格id
     * @return 购物车信息
     */
    @Select("select shopcart_id,uid,shop_id,num,bady_sex,bady_age_id,shop_spec_price,status,addtime,addip,isdisabled from yls_shopcart " +
            "where " +
            "uid=#{uid} and shop_id=#{shop_id} and bady_sex=#{bady_sex} and bady_age_id=#{bady_age_id} and shop_spec_price=#{shop_spec_price} and status=#{status} and isdisabled=1")
    Map<String, Object> selectShopCartInfoByUidAndShopIdAndSexAndAgeAndShopSpecPrice(Map<String, Object> shopcart);

    /**
     * 修改购物车信息
     * @param shopcartinfo 购物车信息
     * @return
     */
    @Update("update yls_shopcart set " +
            "uid=#{uid},shop_id=#{shop_id},num=#{num},bady_sex=#{bady_sex},bady_age_id=#{bady_age_id},shop_spec_price=#{shop_spec_price},status=#{status},addtime=#{addtime},addip=#{addip},isdisabled=#{isdisabled} " +
            "where shopcart_id=#{shopcart_id}")
    int updateShopCartInfo(Map<String, Object> shopcartinfo);

    /**
     * 根据用户id查询用户购物车信息
     * @param uid 用户id
     * @return 购物车信息
     */
    @Select("select sh.shop_name,p.price,p.first,p.second,p.third,p.forth,p.fifth,p.img,sp.name,c.num,c.shopcart_id,c.shop_id,c.bady_sex,c.bady_age_id,c.shop_spec_price " +
            "from yls_shopcart c, yls_shopping sh, yls_shop_spec sp,yls_shop_spec_price p " +
            "where " +
            "c.uid=#{uid} and c.isdisabled=1 and c.`status`=1 and sh.isdisabled=1 and sh.`status`=1 and sp.isdisabled=1 and p.isdisabled=1 " +
            "and c.shop_id=sh.shop_id and c.shop_spec_price=p.shop_spec_price_id and sp.spec_id=p.spec_id")
    List<Map<String,Object>> selectShopCartByUid(String uid);

    /**
     * 根据购物车id查询购物车信息
     * @param shopcartid 购物车id
     * @return 购物车信息
     */
    @Select("select shopcart_id,uid,shop_id,num,bady_sex,bady_age_id,shop_spec_price,status,addtime,addip,isdisabled from yls_shopcart " +
            "where shopcart_id=#{shopcartid} and isdisabled=1")
    Map<String,Object> selectShopCartInfoById(String shopcartid);

    /**
     * 删除购物车信息
     * @param shopcart_id 购物车id
     * @return 删除状态（大于0成功，否则失败）
     */
    @Delete("delete from yls_shopcart where shopcart_id=#{shopcart_id}")
    int deleteShopCartInfo(String shopcart_id);

    /**
     * 根据用户id查询用户收藏故事信息
     * @param map 用户id及分页信息
     * @return 收藏故事信息
     */
    @Select("select s.story_id,s.story_name,s.brief,s.story_logo,s.type,s.shopid from yls_story s,yls_collect c " +
            "where s.status=1 and s.isdisabled=1 and c.uid=#{uid} and c.status=1 and c.story_id=s.story_id order by s.sticktime desc limit #{start},#{count} ")
    List<Map<String,Object>> selectCollectStoryByUid(Map<String, Object> map);

    /**
     * 根据用户id和故事id删除我的收藏
     * @param map 用户id和故事id
     * @return 删除状态（大于0成功，否则失败）
     */
    @Delete("delete from yls_collect where story_id=#{storyid} and uid=#{uid}")
    int deleteMyCollect(Map<String, Object> map);

    /**
     * 根据商品规格价格id查询商品规格价格信息
     * @param shopspecpriceid 商品规格价格id
     * @return 商品规格价格信息
     */
    @Select("select shop_spec_price_id,shop_id,spec_id,price,first,second,third,forth,fifth,img from yls_shop_spec_price where shop_spec_price_id=#{shopspecpriceid}")
    Map<String,Object> selectShopSpecPriceById(String shopspecpriceid);

    /**
     * 根据商品id和商品数量查询商品优惠信息
     * @param shopfavorable 商品id和数量
     * @return 商品优惠信息
     */
    @Select("select lose from yls_shop_favorable where shop_id=#{shopid} and number <= #{number} order by number desc limit 0,1")
    Map<String,Object> selectShopFavorabledByShopIdAndNum(Map<String, Object> shopfavorable);

    /**
     * 获取商品列表信息
     * @param map
     * @returnp
     */
    @Select("select sh.shop_name,sh.shop_id,p.price,p.first,p.second,p.third,p.forth,p.fifth,p.img " +
            "from yls_shopping sh, yls_shop_spec_price p " +
            "where " +
            "sh.isdisabled=1 and sh.`status`=1 and p.isdisabled=1 and p.shop_id=sh.shop_id group by sh.shop_id limit #{start}, #{count}")
    List<Map<String,Object>> selectShopInfo(Map<String, Object> map);

    /**
     * 获取商品价格列表信息
     * @return 商品价格列表信息
     */
    @Select("select sh.shop_name shopname,sh.shop_id,p.price,p.first,p.second,p.third,p.forth,p.fifth,spec.name " +
            "from yls_shopping sh, yls_shop_spec_price p ,yls_shop_spec spec " +
            "where " +
            "sh.isdisabled=1 and sh.`status`=1 and p.isdisabled=1 and p.shop_id=sh.shop_id and p.spec_id=spec.spec_id " +
            "order by sh.shop_id")
    List<Map<String,Object>> selectShopPriceList();
}